package com.mp.approvalreq.dao.impl;


import com.alibaba.fastjson.JSONArray;
import com.mp.approvalreq.dao.SelectTemplateDao;
import com.mp.approvalreq.entity.Template;
import com.mp.approvalreq.entity.dto.*;
import com.mp.approvalreq.entity.po.ComponentGroup;
import com.mp.approvalreq.entity.vo.Component;
import com.mp.approvalreq.util.DateUtils;
import com.plusmoney.util.SQLLogger;
import com.plusmoney.util.Utils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author xq
 */
public class SelectTemplateDaoImpl implements SelectTemplateDao {
    private static final Logger logger = LoggerFactory.getLogger(SelectApprovalDaoImpl.class);

    private Connection conn;

    private PreparedStatement getPrepareStatement(String sql) throws SQLException {
        return conn.prepareStatement(sql);
    }

    public SelectTemplateDaoImpl(Connection conn) {
        this.conn = conn;
    }

    @Override
    public List<TemplateInfoDTO> selectTemplateUsedTimes(Integer companyId) throws SQLException {
        List<TemplateInfoDTO> templateInfoDTOs = new ArrayList<>();

        String sql = " select count(*), Ftemplate_default_type from approval.request_template " +
         " where Fcompany_id = ? and Fstatus = 1 group by Ftemplate_default_type ";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    TemplateInfoDTO templateInfoDTO = new TemplateInfoDTO();
                    templateInfoDTO.setTemplateUsedTimes(rs.getInt(1));
                    templateInfoDTO.setDefaultType(rs.getInt(2));

                    templateInfoDTOs.add(templateInfoDTO);
                }
            }
        }

        return templateInfoDTOs;
    }

    @Override
    public Map<Integer, List<TemplateRangeDTO>> getTemplatesGroupByType(Integer companyId) throws SQLException {
        Map<Integer, List<TemplateRangeDTO>> templateTypeMap = new HashMap<>();
        List<TemplateRangeDTO> templateRangeDTOs = new ArrayList<>();

        String sql = " select Ftemplate_id, Ftemplate_default_type, Fdepartment_id, Frange_specific, Fcompany_info_id_range " +
         " from approval.request_template " +
         " where Fcompany_id = ? and Fstatus = 1 ";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    TemplateRangeDTO templateRangeDTO = new TemplateRangeDTO();
                    templateRangeDTO.setTemplateId(rs.getInt(1));
                    templateRangeDTO.setTemplateType(rs.getInt(2));
                    templateRangeDTO.setDepartmentIds(rs.getString(3));
                    templateRangeDTO.setContactIdRange(rs.getString(4));
                    templateRangeDTO.setCompanyInfoIdRange(rs.getString(5));

                    templateRangeDTOs.add(templateRangeDTO);
                }
            }
        }

        for (int i = Template.EXPENSES; i <= Template.DAYS_OFF; i++) {
            templateTypeMap.put(i, new ArrayList<>());
        }

        for (TemplateRangeDTO templateRangeDTO : templateRangeDTOs) {
            List<TemplateRangeDTO> rangeDTOs = templateTypeMap.get(templateRangeDTO.getTemplateType());
            rangeDTOs.add(templateRangeDTO);
        }

        return templateTypeMap;
    }


    @Override
    public List<Template> getTemplatesRangeByType(Integer defaultType, Integer companyId) throws SQLException {
        List<Template> list = new LinkedList<>();

        String sql = " select Ftemplate_id, Fdepartment_id, Frange_specific, Fcompany_info_id_range " +
         " from approval.request_template " +
         " where Fcompany_id = ? and Ftemplate_default_type = ? and Fstatus = 1 ";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setInt(2, defaultType);

            logger.info(SQLLogger.logSQL(sql, companyId, defaultType));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Template template = new Template();
                    template.setTemplateId(rs.getInt(1));
                    template.setDepartmentId(rs.getString(2));
                    template.setRangeSpecific(rs.getString(3));
                    template.setCompanyInfoIdRange(rs.getString(4));

                    list.add(template);
                }
            }
        }

        return list;
    }


    @Override
    public TemplateDTO getTemplateDTO(Integer defaultType) throws SQLException {
        TemplateDTO templateDTO = new TemplateDTO();

        String sql = " select Ftemplate_id, Fancestor_id, Fis_allow_add_temp_cc, Fgeneration " +
         " from approval.request_template where Ftemplate_id = ? ";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, defaultType);
            logger.info(SQLLogger.logSQL(sql, defaultType));

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    templateDTO.setTemplateId(rs.getInt(1));
                    templateDTO.setAncestorId(rs.getInt(2));
                    templateDTO.setIsAllowAddTempCc(rs.getInt(3));
                    templateDTO.setGeneration(rs.getInt(4));
                }
            }
        }

        return templateDTO;
    }


    @Override
    public TemplateDTO getTemplateDTO(Integer companyId, Integer templateId) throws SQLException {
        TemplateDTO templateDTO = new TemplateDTO();

        String sql = " select Ftemplate_id, Fancestor_id, Fis_allow_add_temp_cc, Fgeneration, " +
         " Fcomponent_group_json, Fout_sign_offset, Ftemplate_name " +
         " from approval.request_template where Ftemplate_id = ? and Fcompany_id = ? ";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, templateId);
            ps.setInt(2, companyId);
            logger.info(SQLLogger.logSQL(sql, templateId, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    templateDTO.setTemplateId(rs.getInt(1));
                    templateDTO.setAncestorId(rs.getInt(2));
                    templateDTO.setIsAllowAddTempCc(rs.getInt(3));
                    templateDTO.setGeneration(rs.getInt(4));
                    templateDTO.setComponentGroupJson(rs.getString(5));
                    templateDTO.setOutSignOffset(rs.getString(6));
                    templateDTO.setTemplateName(rs.getString(7));
                }
            }
        }

        return templateDTO;
    }


    @Override
    public Map<Integer, TemplateDTO> getTemplateDTOMap(Integer companyId, List<Integer> templateIds) throws SQLException {
        Map<Integer, TemplateDTO> map = new HashMap<>();

        //空则返回
        if (Utils.isEmpty(templateIds)) {
            return map;
        }

        StringBuilder sql = new StringBuilder(" select Ftemplate_id, Ftemplate_name, Ftemplate_color, Fis_related_to_worktime, " +
         " Fdepartment_id, Frange_specific, Fcreatetime, Fdisabletime from approval.request_template " +
         " where Fcompany_id = ? and Ftemplate_id in(");
        for (int i = 0; i < templateIds.size(); i++) {
            sql.append(templateIds.get(i));
            if (templateIds.size() - i > 1) {
                sql.append(",");
            }
        }
        sql.append(")");

        SimpleDateFormat sdf = DateUtils.getSimpleDateFormat(DateUtils.YMDHMS);

        try (PreparedStatement ps = getPrepareStatement(sql.toString())) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql.toString(), companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    TemplateDTO templateDTO = new TemplateDTO();
                    templateDTO.setTemplateId(rs.getInt(1));
                    templateDTO.setTemplateName(rs.getString(2));
                    templateDTO.setTemplateColor(rs.getString(3));
                    templateDTO.setIsRelatedWorkTime(rs.getInt(4));
                    templateDTO.setDepartmentIds(rs.getString(5));
                    templateDTO.setSpecificRange(rs.getString(6));
                    templateDTO.setCreateTime(rs.getString(7) == null ? null : sdf.format(rs.getTimestamp(7)));
                    templateDTO.setDisableTime(rs.getString(8) == null ? null : sdf.format(rs.getTimestamp(8)));

                    map.put(rs.getInt(1), templateDTO);
                }
            }
        }

        return map;
    }


    @Override
    public List<TemplateDTO> getAssociateCostTemplateDTOs(Integer companyId) throws SQLException {
        List<TemplateDTO> templateDTOS = new LinkedList<>();

        String sql = " select Ftemplate_id, Fis_related_to_cost_analysis, Frelate_analysis_num " +
         " from approval.request_template " +
         " where Fcompany_id = ? and Fis_related_to_cost_analysis = 1 ";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, companyId);

            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    TemplateDTO templateDTO = new TemplateDTO();
                    templateDTO.setTemplateId(rs.getInt(1));
                    templateDTO.setIsRelatedCostAnalysis(rs.getInt(2));
                    templateDTO.setRelateAnalysisNum(rs.getString(3));
                    templateDTOS.add(templateDTO);
                }
            }
        }

        return templateDTOS;
    }


    @Override
    public List<Integer> getAllTemplateIdList(Integer companyId) throws SQLException {
        List<Integer> templateIds = new ArrayList<>();

        String sql = " select Ftemplate_id from  request_template where Fcompany_id = ? ";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    templateIds.add(rs.getInt(1));
                }
            }
        }

        return templateIds;
    }


    @Override
    public List<TemplateDTO> selectCompanyTemplates(Integer companyId) throws SQLException {
        List<TemplateDTO> templateDTOs = new LinkedList<>();

        String sql = " select Ftemplate_id, Ftemplate_name, Frequest_flow, Ftemplate_color, " +
         " Ftemplate_default_type, Fcc, Fversion, Famount_top, Fdepartment_id, Fancestor_id, " +
         " Fmax_time_permonth, Fcontent_time_limit, Fis_max_time, Frange_specific, Fcompany_info_id_range " +
         " from request_template where Fcompany_id = ? and Fstatus = 1 ";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    TemplateDTO templateDTO = new TemplateDTO();

                    templateDTO.setTemplateId(rs.getInt(1));
                    templateDTO.setTemplateName(rs.getString(2));
                    templateDTO.setRequestFlow(rs.getString(3));
                    templateDTO.setTemplateColor(rs.getString(4));
                    templateDTO.setDefaultType(rs.getInt(5));
                    templateDTO.setTemplateCc(rs.getString(6));
                    templateDTO.setVersion(rs.getInt(7));
                    templateDTO.setAmountTop(rs.getBigDecimal(8));
                    templateDTO.setDepartmentIds(rs.getString(9));
                    templateDTO.setAncestorId(rs.getInt(10));
                    templateDTO.setMaxTimePermonth(rs.getInt(11));
                    templateDTO.setContentTimeLimit(rs.getInt(12));
                    templateDTO.setIsMaxTime(rs.getInt(13));
                    templateDTO.setSpecificRange(rs.getString(14));
                    templateDTO.setCompanyInfoIdRange(rs.getString(15));

                    templateDTOs.add(templateDTO);
                }
            }
        }

        return templateDTOs;
    }


    @Override
    public Map<Integer, List<ConditionFlowDTO>> getConditionFlowTemplateDTOs(Integer companyId, String templateIds) throws SQLException {
        Map<Integer, List<ConditionFlowDTO>> map = new HashMap<>();
        List<ConditionFlowDTO> conditionFlowDTOs = new ArrayList<>();

        String sql = " select Fid, Ftemplate_id, Fcondition_type, Frela_component_num, Fcondition_calculation_type, "
         + " Fsmaller_factor, Fgreater_factor, Frange, Frequest_flow, Fis_allowed_to_edit_judger,"
         + " Funit_type, FLeft_symbol, Fright_symbol "
         + " from pt_mp_request_condition_flow_desc "
         + " where Fcompany_id = ? and Fstate = 0 and Ftemplate_id in(" + templateIds + ")";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, companyId);

            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    ConditionFlowDTO conditionFlow = new ConditionFlowDTO();
                    conditionFlow.setId(rs.getInt(1));
                    conditionFlow.setTemplateId(rs.getInt(2));
                    conditionFlow.setConditionType(rs.getString(3));
                    conditionFlow.setRelaComponentNum(rs.getString(4));
                    conditionFlow.setConditionCalculationType(rs.getInt(5));
                    conditionFlow.setSmallerFactor(rs.getBigDecimal(6));
                    conditionFlow.setGreaterFactor(rs.getBigDecimal(7));
                    conditionFlow.setRange(rs.getString(8));
                    conditionFlow.setRequestFlow(rs.getString(9));
                    conditionFlow.setIsAllowedToEditJudger(rs.getInt(10));
                    conditionFlow.setUnitType(rs.getInt(11));
                    conditionFlow.setLeftSymbol(rs.getInt(12));
                    conditionFlow.setRightSymbol(rs.getInt(13));

                    conditionFlowDTOs.add(conditionFlow);
                }
            }
        }

        for (ConditionFlowDTO conditionFlowDTO : conditionFlowDTOs) {
            if (map.containsKey(conditionFlowDTO.getTemplateId())) {
                List<ConditionFlowDTO> conditionFlowDTOList = map.get(conditionFlowDTO.getTemplateId());
                conditionFlowDTOList.add(conditionFlowDTO);
            } else {
                List<ConditionFlowDTO> conditionFlowDTOList = new ArrayList<>();
                conditionFlowDTOList.add(conditionFlowDTO);
                map.put(conditionFlowDTO.getTemplateId(), conditionFlowDTOList);
            }
        }

        return map;
    }

    @Override
    public Map<Integer, List<Integer>> selectTemplateIdsByAncestorIds(String ancestorIds) throws SQLException {
        Map<Integer, List<Integer>> map = new HashMap<>();
        if (StringUtils.isBlank(ancestorIds)) {
            return map;
        }
        String sql = "select Fancestor_id, Ftemplate_id from request_template where Fancestor_id in(" + ancestorIds + ")";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            logger.info(sql);

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    if (!map.containsKey(rs.getInt(1))) {
                        map.put(rs.getInt(1), new ArrayList<>());
                    }
                    map.get(rs.getInt(1)).add(rs.getInt(2));
                }
            }
        }
        return map;
    }

    @Override
    public List<TemplateDTO> selectCompanyTemplatesByTemplateType(Integer companyId, Integer templateType) throws SQLException {
        List<TemplateDTO> templateDTOs = new ArrayList<>();

        String sql = " select Ftemplate_id, Ftemplate_name, Frequest_flow, Ftemplate_color, " +
         " Ftemplate_default_type, Fcc, Fversion, Famount_top, Fdepartment_id, Fancestor_id, " +
         " Fmax_time_permonth, Fcontent_time_limit, Fis_max_time, Frange_specific, Fcompany_info_id_range " +
         " from request_template where Fcompany_id = ? and Ftemplate_default_type = ? and Fstatus = 1 ";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setInt(2, templateType);
            logger.info(SQLLogger.logSQL(sql, companyId, templateType));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    TemplateDTO templateDTO = new TemplateDTO();

                    templateDTO.setTemplateId(rs.getInt(1));
                    templateDTO.setTemplateName(rs.getString(2));
                    templateDTO.setRequestFlow(rs.getString(3));
                    templateDTO.setTemplateColor(rs.getString(4));
                    templateDTO.setDefaultType(rs.getInt(5));
                    templateDTO.setTemplateCc(rs.getString(6));
                    templateDTO.setVersion(rs.getInt(7));
                    templateDTO.setAmountTop(rs.getBigDecimal(8));
                    templateDTO.setDepartmentIds(rs.getString(9));
                    templateDTO.setAncestorId(rs.getInt(10));
                    templateDTO.setMaxTimePermonth(rs.getInt(11));
                    templateDTO.setContentTimeLimit(rs.getInt(12));
                    templateDTO.setIsMaxTime(rs.getInt(13));
                    templateDTO.setSpecificRange(rs.getString(14));
                    templateDTO.setCompanyInfoIdRange(rs.getString(15));

                    templateDTOs.add(templateDTO);
                }
            }
        }

        return templateDTOs;
    }

    @Override
    public List<Integer> selectTemplateIdsByName(Integer companyId, String templateName) throws SQLException {
        List<Integer> list = new ArrayList<>();
        String sql = "select Ftemplate_id from request_template where Fcompany_id = ? and Ftemplate_name = ?";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setString(2, templateName);
            logger.info(SQLLogger.logSQL(sql, companyId, templateName));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(rs.getInt(1));
                }
            }
        }
        return list;
    }


    @Override
    public Map<Integer, Integer> getUsedTimesByMonth(Integer companyInfoId, String month) throws SQLException {
        Map<Integer, Integer> map = new HashMap<>();

        String sql = " select Frequest_template_type, count(*) from request_flow " +
         " where Fapplicant_ciid = ? and date_format(Fcreate_time,'%Y-%m') = ? " +
         " and Ffinished in(0,1,2) group by Frequest_template_type ";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, companyInfoId);
            ps.setString(2, month);
            logger.info(SQLLogger.logSQL(sql, companyInfoId, month));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    map.put(rs.getInt(1), rs.getInt(2));
                }
            }
        }

        return map;
    }


    @Override
    public ComponentGroup selectComponentGroupById(Integer companyId, Integer componentGroupId) throws SQLException {
        ComponentGroup componentGroup = new ComponentGroup();

        String sql = " select id, company_id, size, name, is_add_component_group, is_common_group, sub_component " +
         "  from request_component_group where id = ? and company_id = ? limit 1 ";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, componentGroupId);
            ps.setInt(2, companyId);
            logger.info(SQLLogger.logSQL(sql, componentGroupId, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    componentGroup.setId(rs.getInt(1));
                    componentGroup.setCompanyId(rs.getInt(2));
                    componentGroup.setSize(rs.getInt(3));
                    componentGroup.setName(rs.getString(4));
                    componentGroup.setIsAddComponentGroup(rs.getInt(5));
                    componentGroup.setIsCommonGroup(rs.getInt(6));
                    String subComponetStr = rs.getString(7);
                    componentGroup.setComponents(JSONArray.parseArray(subComponetStr, Component.class));
                }
            }
        }

        return componentGroup;
    }


    @Override
    public List<ComponentGroup> selectComponentGroupList(Integer companyId) throws SQLException {
        List<ComponentGroup> list = new ArrayList<>();

        String sql = " select id, company_id, size, name, is_common_group " +
         "  from request_component_group where company_id = ? and state = 0 and is_common_group = 1 ";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    ComponentGroup componentGroup = new ComponentGroup();
                    componentGroup.setId(rs.getInt(1));
                    componentGroup.setCompanyId(rs.getInt(2));
                    componentGroup.setSize(rs.getInt(3));
                    componentGroup.setName(rs.getString(4));
                    componentGroup.setIsCommonGroup(rs.getInt(5));

                    list.add(componentGroup);
                }
            }
        }

        return list;
    }


    @Override
    public List<ComponentGroup> selectComponentGroupInfoList(Integer companyId, String ids) throws SQLException {
        List<ComponentGroup> list = new ArrayList<>();

        //空则返回
        if (StringUtils.isBlank(ids)) {
            return list;
        }

        String sql = " select id, company_id, size, name, is_add_component_group, is_common_group, sub_component " +
         "  from request_component_group where company_id = ? and id in(" + ids + ")";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    ComponentGroup componentGroup = new ComponentGroup();
                    componentGroup.setId(rs.getInt(1));
                    componentGroup.setCompanyId(rs.getInt(2));
                    componentGroup.setSize(rs.getInt(3));
                    componentGroup.setName(rs.getString(4));
                    componentGroup.setIsAddComponentGroup(rs.getInt(5));
                    componentGroup.setIsCommonGroup(rs.getInt(6));
                    componentGroup.setComponents(JSONArray.parseArray(rs.getString(7), Component.class));

                    list.add(componentGroup);
                }
            }
        }

        return list;
    }

}
